﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections;
using Aspose.Cells;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Reflection;
namespace WebKpiPage.BLL.Common
{
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
    public class ExcelFieldAttribute : System.Attribute
    {
        private string _colname;
        private int _idx;
        private string _fieldname;

        public ExcelFieldAttribute(string colname, int index)
        {
            this._colname = colname;
            this._idx = index;
        }

        public string ColName
        {
            get { return _colname; }
            set { _colname = value; }
        }

        public int Index
        {
            get { return _idx; }
            set { _idx = value; }
        }

        public string FieldName
        {
            get { return _fieldname; }
            set { _fieldname = value; }
        }
    }

    /// <summary>
    /// 利用Aspose.Cells导出和导入Excel
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 导出Excel，只有一个Sheet
        /// <para></para>
        /// 将DataTable内容导出到Excel中
        /// <para></para>
        /// 以DataTable名作为Sheet名
        /// </summary>
        /// <param name="tables">导出内容DataTable，以DataTable名作为Sheet名</param>
        /// <param name="filename">导出文件名称</param>
        /// <returns></returns>
        public static bool ExportToExcelFromTable(DataTable table, string filename)
        {
            bool result = false;
            if (table == null)
            {
                return result;
            }
            try
            {
                Workbook workbook = new Workbook();
                Worksheet worksheet = workbook.Worksheets[0];
                if (table.TableName == null && table.TableName.Trim().Equals(string.Empty))
                {
                    worksheet.Name = "Sheet1";
                }
                else
                {
                    worksheet.Name = table.TableName;
                }
                worksheet.AutoFitColumns();
                Cells cells = worksheet.Cells;

                int colidx = 0;
                int rowidx = 0;

                // 设置 Excel 工作薄的列名
                foreach (DataColumn col in table.Columns)
                {
                    cells[rowidx, colidx].Style.Font.IsBold = true;
                    cells[rowidx, colidx].Style.HorizontalAlignment = TextAlignmentType.Center;
                    cells[rowidx, colidx++].PutValue(col.ColumnName);
                }

                // 将数据按顺序插入各 cell
                foreach (DataRow row in table.Rows)
                {
                    colidx = 0;
                    rowidx++;
                    foreach (DataColumn col in table.Columns)
                    {
                        cells[rowidx, colidx++].PutValue(row[col.ColumnName]);
                    }
                }
                // //HttpContext.Current.Server.UrlPathEncode(filename)
                filename = System.Web.HttpUtility.UrlEncode(filename);//设置导出文件的名称
                workbook.Save(filename, FileFormatType.Default, SaveType.OpenInExcel, HttpContext.Current.Response, System.Text.ASCIIEncoding.UTF8);
             //  string path = Server.MapPath("~") + "\\Excel\\download\\" + fileName + ".xls";

             
        
                result = true;
            }
            catch (Exception ex)
            {
                //LogHelper.WriteErrorLog(ex);
            }

            return result;
        }

        public static byte[] ExportToExcelBytesFromTable(DataTable table, string filename)
        {
            if (table == null)
            {
                return null;
            }
            try
            {
                Workbook workbook = new Workbook();
                Worksheet worksheet = workbook.Worksheets[0];
                if (table.TableName == null && table.TableName.Trim().Equals(string.Empty))
                {
                    worksheet.Name = "Sheet1";
                }
                else
                {
                    worksheet.Name = table.TableName;
                }
                worksheet.AutoFitColumns();
                Cells cells = worksheet.Cells;

                int colidx = 0;
                int rowidx = 0;

                // 设置 Excel 工作薄的列名
                foreach (DataColumn col in table.Columns)
                {
                    cells[rowidx, colidx].Style.Font.IsBold = true;
                    cells[rowidx, colidx].Style.HorizontalAlignment = TextAlignmentType.Center;
                    cells[rowidx, colidx++].PutValue(col.ColumnName);
                }

                // 将数据按顺序插入各 cell
                foreach (DataRow row in table.Rows)
                {
                    colidx = 0;
                    rowidx++;
                    foreach (DataColumn col in table.Columns)
                    {
                        cells[rowidx, colidx++].PutValue(row[col.ColumnName]);
                    }
                }
                
                byte[] rtnBytes=workbook.SaveToStream().ToArray();
                return rtnBytes;

            }
            catch (Exception ex)
            {
                //LogHelper.WriteErrorLog(ex);
            }

            return null;
        }

        /// <summary>
        /// 导出Excel，可以有多个Sheet
        /// <para></para>
        /// 将DataTable数组内容导出到Excel中
        /// <para></para>
        /// 一个DataTable对应一个Sheet，以DataTable名作为Sheet名
        /// </summary>
        /// <param name="tables">导出内容DataTable数组，一个DataTable对应一个Sheet，以DataTable名作为Sheet名</param>
        /// <param name="filename">导出文件名称</param>
        /// <returns></returns>
        public static bool ExportToExcelFromTables(DataTable[] tables, string filename)
        {
            bool result = false;
            if (tables == null)
            {
                return result;
            }
            try
            {
                Workbook workbook = new Workbook();
                workbook.Worksheets.Clear();
                for (int i = 0; i < tables.Length; i++)
                {
                    DataTable table = tables[i];
                    if (table == null)
                    {
                        continue;
                    }
                    workbook.Worksheets.Add();
                    Worksheet worksheet = workbook.Worksheets[workbook.Worksheets.Count - 1];
                    if (table.TableName == null && table.TableName.Trim().Equals(string.Empty))
                    {
                        worksheet.Name = "Sheet" + (i + 1);
                    }
                    else
                    {
                        worksheet.Name = table.TableName;
                    }
                    worksheet.AutoFitColumns();
                    Cells cells = worksheet.Cells;

                    int colidx = 0;
                    int rowidx = 0;

                    // 设置 Excel 工作薄的列名
                    foreach (DataColumn col in table.Columns)
                    {
                        cells[rowidx, colidx].Style.Font.IsBold = true;
                        cells[rowidx, colidx].Style.HorizontalAlignment = TextAlignmentType.Center;
                        cells[rowidx, colidx++].PutValue(col.ColumnName);
                    }

                    // 将数据按顺序插入各 cell
                    foreach (DataRow row in table.Rows)
                    {
                        colidx = 0;
                        rowidx++;
                        foreach (DataColumn col in table.Columns)
                        {
                            cells[rowidx, colidx++].PutValue(row[col.ColumnName]);
                        }
                    }
                }
                if (workbook.Worksheets.Count == 0)
                {
                    workbook.Worksheets.Add();
                }
                workbook.Save(filename, FileFormatType.Default, SaveType.OpenInExcel, HttpContext.Current.Response);
                result = true;
            }
            catch (Exception ex)
            {
                //LogHelper.WriteErrorLog(ex);
            }

            return result;
        }

        /// <summary>
        /// 将DataTable数据生成Excel文件并下载
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="filename">Excel 文件名</param>
        /// <param name="cols">数据与 Excel 列对应关系</param>
        /// <returns>是否导出成功</returns>
        public static bool ExportToExcel(DataTable data, string filename, IList<Pair> cols)
        {
            bool result = false;
            try
            {
                Workbook workbook = new Workbook();
                Worksheet worksheet = workbook.Worksheets[0];
                worksheet.AutoFitColumns();
                Cells cells = worksheet.Cells;

                int colidx = 0;
                int rowidx = 0;

                // 设置 Excel 工作薄的列名
                foreach (Pair col in cols)
                {
                    cells[rowidx, colidx].Style.Font.IsBold = true;
                    cells[rowidx, colidx].Style.HorizontalAlignment = TextAlignmentType.Center;
                    cells[rowidx, colidx++].PutValue(col.First);
                }

                // 将数据按顺序插入各 cell
                foreach (DataRow row in data.Rows)
                {
                    colidx = 0;
                    rowidx++;
                    foreach (Pair col in cols)
                    {
                        cells[rowidx, colidx++].PutValue(row[col.Second.ToString()]);
                    }
                }

                workbook.Save(filename, FileFormatType.Default, SaveType.OpenInExcel, HttpContext.Current.Response);
                result = true;
            }
            catch (Exception ex)
            {
                //LogHelper.WriteErrorLog(ex);
            }

            return result;
        }

        /// <summary>
        /// 将DataTable数据依照模板文件生成Excel文件并下载
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="templatename">Excel 模板文件名</param>
        /// <param name="filename">Excel 文件名</param>
        /// <param name="cols">数据与 Excel 列对应关系</param>
        /// <param name="startrow">在 Excel 插入数据的行号</param>
        /// <returns>是否导出成功</returns>
        public static bool ExportToExcel(DataTable data, string templatename, string filename, ArrayList cols, int startrow, int startcol)
        {
            bool result = false;

            try
            {
                Workbook workbook = new Workbook();
                workbook.Open(templatename);
                Worksheet worksheet = workbook.Worksheets[0];
                Cells cells = worksheet.Cells;

                int colidx = startcol;
                int rowidx = startrow;

                // 将数据按顺序插入各 cell
                foreach (DataRow row in data.Rows)
                {
                    colidx = startcol;
                    foreach (string col in cols)
                    {
                        cells[rowidx, colidx++].PutValue(row[col]);
                    }
                    rowidx++;
                }

                workbook.Save(filename, FileFormatType.Default, SaveType.OpenInExcel, HttpContext.Current.Response);

                result = true;
            }
            catch (Exception ex)
            {
                //LogHelper.WriteErrorLog(ex);
            }

            return result;
        }

        /// <summary>
        /// 将DataTable数据生成Excel文件并下载
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="filename">Excel 文件名</param>
        /// <returns>是否导出成功</returns>
        public static bool ExportToExcel<T>(IList<T> data, string filename)
        {
            bool result = false;

            Type t = typeof(T);
            Dictionary<int, Pair> fields = new Dictionary<int, Pair>();
            foreach (PropertyInfo proinfo in t.GetProperties())
            {
                object[] attrs = proinfo.GetCustomAttributes(typeof(ExcelFieldAttribute), true);
                foreach (object attr in attrs)
                {
                    ExcelFieldAttribute field = attr as ExcelFieldAttribute;

                    if (fields.ContainsKey(field.Index))
                        continue;

                    fields.Add(field.Index, new Pair(proinfo, field.ColName));
                }
            }

            try
            {
                Workbook workbook = new Workbook();
                Worksheet worksheet = workbook.Worksheets[0];
                worksheet.AutoFitColumns();
                Cells cells = worksheet.Cells;

                int rowidx = 0;

                // 设置 Excel 工作薄的列名                 
                foreach (int idx in fields.Keys)
                {
                    cells[rowidx, idx].Style.Font.IsBold = true;
                    cells[rowidx, idx].Style.HorizontalAlignment = TextAlignmentType.Center;
                    cells[rowidx, idx].PutValue(fields[idx].Second);
                }

                // 将数据按顺序插入各 cell
                foreach (T model in data)
                {
                    rowidx++;
                    foreach (int idx in fields.Keys)
                    {
                        PropertyInfo p = fields[idx].First as PropertyInfo;
                        object value = p.GetValue(model, null);
                        cells[rowidx, idx].PutValue(value);
                    }
                }

                workbook.Save(filename, FileFormatType.Default,
                    SaveType.OpenInExcel, HttpContext.Current.Response);

                result = true;
            }
            catch (Exception ex)
            {
                //LogHelper.WriteErrorLog(ex);
            }

            return result;
        }

        /// <summary>
        /// 将DataTable数据依照模板文件生成Excel文件并下载
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="templatename">Excel 模板文件名</param>
        /// <param name="filename">Excel 文件名</param>
        /// <param name="cols">数据与 Excel 列对应关系</param>
        /// <param name="startrow">在 Excel 插入数据的行号</param>
        /// <returns>是否导出成功</returns>
        public static bool ExportToExcel<T>(IList<T> data, string templatename, string filename, int startrow, int startcol)
        {
            bool result = false;

            Type t = typeof(T);
            Dictionary<int, Pair> fields = new Dictionary<int, Pair>();
            foreach (PropertyInfo proinfo in t.GetProperties())
            {
                object[] attrs = proinfo.GetCustomAttributes(typeof(ExcelFieldAttribute), true);
                foreach (object attr in attrs)
                {
                    ExcelFieldAttribute field = attr as ExcelFieldAttribute;

                    if (fields.ContainsKey(field.Index))
                        continue;

                    fields.Add(field.Index, new Pair(proinfo, field.ColName));
                }
            }

            try
            {
                Workbook workbook = new Workbook();
                workbook.Open(templatename);
                Worksheet worksheet = workbook.Worksheets[0];
                Cells cells = worksheet.Cells;

                int colidx = startcol;
                int rowidx = startrow;

                // 将数据按顺序插入各 cell
                foreach (T model in data)
                {
                    foreach (int idx in fields.Keys)
                    {
                        PropertyInfo p = fields[idx].First as PropertyInfo;
                        object value = p.GetValue(model, null);
                        cells[rowidx, colidx + idx].PutValue(value);
                    }
                    rowidx++;
                }

                workbook.Save(filename, FileFormatType.Default, SaveType.OpenInExcel, HttpContext.Current.Response);

                result = true;
            }
            catch (Exception ex)
            {
                //LogHelper.WriteErrorLog(ex);
            }

            return result;
        }

        /// <summary>
        /// 将DataTable数据生成Excel文件并下载
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="filename">Excel 文件名</param>
        /// <returns>是否导出成功</returns>
        public static MemoryStream ExportToStream<T>(IList<T> data, string filename)
        {
            Type t = typeof(T);
            Dictionary<int, Pair> fields = new Dictionary<int, Pair>();
            foreach (PropertyInfo proinfo in t.GetProperties())
            {
                object[] attrs = proinfo.GetCustomAttributes(typeof(ExcelFieldAttribute), true);
                foreach (object attr in attrs)
                {
                    ExcelFieldAttribute field = attr as ExcelFieldAttribute;

                    if (fields.ContainsKey(field.Index))
                        continue;

                    fields.Add(field.Index, new Pair(proinfo, field.ColName));
                }
            }

            try
            {
                Workbook workbook = new Workbook();
                Worksheet worksheet = workbook.Worksheets[0];
                worksheet.AutoFitColumns();
                Cells cells = worksheet.Cells;

                int rowidx = 0;

                // 设置 Excel 工作薄的列名                 
                foreach (int idx in fields.Keys)
                {
                    cells[rowidx, idx].Style.Font.IsBold = true;
                    cells[rowidx, idx].Style.HorizontalAlignment = TextAlignmentType.Center;
                    cells[rowidx, idx].PutValue(fields[idx].Second);
                }

                // 将数据按顺序插入各 cell
                foreach (T model in data)
                {
                    rowidx++;
                    foreach (int idx in fields.Keys)
                    {
                        PropertyInfo p = fields[idx].First as PropertyInfo;
                        object value = p.GetValue(model, null);
                        cells[rowidx, idx].PutValue(value);
                    }
                }

                workbook.Save(filename, FileFormatType.Default);
                return workbook.SaveToStream();
            }
            catch (Exception ex)
            {
                //LogHelper.WriteErrorLog(ex);
            }
            return null;
        }

        #region 读取Excel文件
        /// <summary>
        /// 读取Excel文件
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <returns></returns>
        public static string[,] ReadFromExcel(string fileName, ref string retMsg)
        {
            string[,] values = null;
            FileInfo fileInfo = new FileInfo(fileName);
            if (!fileInfo.Exists)
            {
                retMsg = "导入文件不存在。";
                return values;
            }
            if (!fileInfo.Extension.Equals(".xls", StringComparison.OrdinalIgnoreCase) 
                && !fileInfo.Extension.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
            {
                retMsg = "导入文件不是Excel文件。";
                return values;
            }
            Aspose.Cells.Cells cells;
            Workbook workbook = new Workbook();
            workbook.Open(fileName); // excel所在路径
            if (workbook.Worksheets.Count < 1)
            {
                retMsg = "导入Excel文件必须有一个工作表。";
                return values;
            }
            cells = workbook.Worksheets[0].Cells; // 第一个sheet
            if (cells == null)
            {
                retMsg = "导入Excel文件出错。";
                return values;
            }
            values = new string[cells.MaxDataRow + 1, cells.MaxDataColumn + 1];
            for (int i = 0; i < cells.MaxDataRow + 1; i++)
            {// 将内容都输出
                for (int j = 0; j < cells.MaxDataColumn + 1; j++)
                {
                    values[i, j] = cells[i, j].StringValue;
                }
            }
            return values;
        }
        #endregion

        #region 读取多个sheet的Excel文件
        /// <summary>
        /// 读取Excel文件
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="sheet">第几个sheet,从0开始</param>
        /// <returns></returns>
        public static string[,] ReadFromExcels(string fileName, ref string retMsg,int sheet)
        {
            string[,] values = null;
            FileInfo fileInfo = new FileInfo(fileName);
            if (!fileInfo.Exists)
            {
                retMsg = "导入文件不存在。";
                return values;
            }
            if (!fileInfo.Extension.Equals(".xls", StringComparison.OrdinalIgnoreCase)
                && !fileInfo.Extension.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
            {
                retMsg = "导入文件不是Excel文件。";
                return values;
            }
            Aspose.Cells.Cells cells;
            Workbook workbook = new Workbook();
            workbook.Open(fileName); // excel所在路径
            if (workbook.Worksheets.Count < 1)
            {
                retMsg = "导入Excel文件必须有一个工作表。";
                return values;
            }
            cells = workbook.Worksheets[sheet].Cells; // 第一个sheet
            if (cells == null)
            {
                retMsg = "导入Excel文件出错。";
                return values;
            }
            values = new string[cells.MaxDataRow + 1, cells.MaxDataColumn + 1];
            for (int i = 0; i < cells.MaxDataRow + 1; i++)
            {// 将内容都输出
                for (int j = 0; j < cells.MaxDataColumn + 1; j++)
                {
                    values[i, j] = cells[i, j].StringValue;
                }
            }
            return values;
        }

        public static string[,] ReadFromExcelsStream(Stream str, ref string retMsg, int sheet)
        {
            string[,] values = null;
            //FileInfo fileInfo = new FileInfo(fileName);
            //if (!fileInfo.Exists)
            //{
            //    retMsg = "导入文件不存在。";
            //    return values;
            //}
            //if (!fileInfo.Extension.Equals(".xls", StringComparison.OrdinalIgnoreCase)
            //    && !fileInfo.Extension.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
            //{
            //    retMsg = "导入文件不是Excel文件。";
            //    return values;
            //}
            Aspose.Cells.Cells cells;
            Workbook workbook = new Workbook();
            workbook.Open(str);
           // workbook.Open(fileName); // excel所在路径
            if (workbook.Worksheets.Count < 1)
            {
                retMsg = "导入Excel文件必须有一个工作表。";
                return values;
            }
            cells = workbook.Worksheets[sheet].Cells; // 第一个sheet
            if (cells == null)
            {
                retMsg = "导入Excel文件出错。";
                return values;
            }
            values = new string[cells.MaxDataRow + 1, cells.MaxDataColumn + 1];
            for (int i = 0; i < cells.MaxDataRow + 1; i++)
            {// 将内容都输出
                for (int j = 0; j < cells.MaxDataColumn + 1; j++)
                {
                    values[i, j] = cells[i, j].StringValue;
                }
            }
            return values;
        }

        #endregion
    }
}
